如何使用Python对MySQL的误操作快速恢复数据？(建议收藏)
The following article is from Python自动化运维 Author 小安Sir
魔幻的故事
天快要亮了，小明因种种原因，上线失败，急需要上线前的一份Mysql全库备份用于回退，无奈此库太大，约20G左右，恢复需要一定的时间。“客服电话已被打爆，大量用户投诉无法登陆，领导非常恼火。问多久能恢复数据？”
他急忙找到公司DBA请求帮助，DBA一脸懵逼，好家伙，强行上线不带回退方案呀，沉默十秒后，伸出一根手指。“你的意思是一分钟就能恢复？太好了。”小明终于有些放松，露出了一丝笑容。“不，懂王我要开始装X了。”DBA不屑地说道。
DBA确认了下误操作的大致有哪些，时间范围是什么？是否有DDL操作，检查Mysql参数等等，发现天时地利人和，最后他翻出了沉淀已久的工具binlog2sql。
binlog2sql究竟是什么，它如何使用？是官方原厂的，还是开源的......
额，官方原厂的不一定好用，开源的东西不一定差，合适才是最好！还是且听小安Sir的分享吧。
1. binlog2sql的相关信息
1.1 为什么会有binlog2sql？
binlog2sql由美团点评DBA团队(上海)出品，多次在线上环境做快速回滚。
DBA或开发人员，有时会误删或者误更新数据，如果是线上环境并且影响较大，就需要能快速回滚。传统恢复方法是利用备份重搭实例，再应用去除错误sql后的binlog来恢复数据。此法费时费力，甚至需要停机维护，并不适合快速回滚。也有团队利用LVM快照来缩短恢复时间，但快照的缺点是会影响mysql的性能。
MySQL闪回(flashback)利用binlog直接进行回滚，能快速恢复且不用停机。
1.2 闪回原理
MySQL binlog以event的形式，记录了MySQL server从启用binlog以来所有的变更信息，能够帮助重现这之间的所有变化。MySQL引入binlog主要有两个目的：一是为了主从复制；二是某些备份还原操作后需要重新应用binlog。
既然binlog以event形式记录了所有的变更信息，那么我们把需要回滚的event，从后往前回滚回去即可。
1.3 使用binlog2sql的基本要求
1) python版本 ：Python 2.7, 3.4+(3.7报错)
2) Mysql版本 : MySQL 5.6, 5.7
3) pip包版本 : PyMySQL==0.7.11
wheel==0.29.0
mysql-replication==0.13
第2节 代码浅析
我们来一起看下这个代码的构建，这里面不仅有主文件binlog2sql.py，而且还有很多的相关文件进行说明，是一份不可多得的学习和实战材料。
2.1 如何实现闪回功能
1) 判断Python的版本
2) 判断时间格式
3) 命令解析器
4) 输入参数检验
5) 转换DML值的格式
6) 支持回滚的类型
7) 回滚模板
8) 魔法方法 __init__
主要是连接Mysql数据库和执行一些简单的Mysql命令。
9) 回滚SQL的函数
本次代码浅析就到这里了，下次再详细分享下，毕竟看别人的代码也是非常耗精力的。
第3节 误操作快速恢复演示
3.1 Python安装pip包
1) 查看python版本
python版本为3.6.5
2) 更新PIP版本
3) 安装指定版本的pip包
按照作者要求安装指定版本的pip包，小安Sir试过直接安装最新版本，发现有一些报错，导致无法使用bin2log的功能。
1[root@PYMY-DB02 mysql]# pip3 install PyMySQL==0.7.11
2[root@PYMY-DB02 mysql]# pip3 install wheel==0.29.0
3[root@PYMY-DB02 mysql]# pip3 install mysql-replication==0.13
3.2 初步测试binlog2sql的功能1) 解压压缩包
[root@PYMY-DB02 mysql]# unzip binlog2sql-master.zip
2) 查看文件夹内容
[root@PYMY-DB02 binlog2sql-master]# ls -l
total 64
drwxr-xr-x 3 root root 4096 Nov 7 17:17 binlog2sql
drwxr-xr-x 2 root root 4096 Oct 12 2018 example
-rw-r--r-- 1 root root 35141 Oct 12 2018 LICENSE
-rw-r--r-- 1 root root 9514 Oct 12 2018 README.md
-rw-r--r-- 1 root root 54 Oct 12 2018 requirements.txt
drwxr-xr-x 2 root root 4096 Oct 12 2018 tests
3) 执行文件binlog2sql.py文件是用于数据恢复的主要文件。
[root@PYMY-DB02 binlog2sql]# ls -l
total 28
-rwxr-xr-x 1 root root 7747 Oct 12 2018 binlog2sql.py
-rwxr-xr-x 1 root root 11581 Oct 12 2018 binlog2sql_util.py
-rw-r--r-- 1 root root 92 Oct 12 2018 __init__.py
drwxr-xr-x 2 root root 4096 Nov 7 17:17 __pycache__
看看执行binlog2sql.py有什么选项。
[root@PYMY-DB02 binlog2sql]# python3 binlog2sql.py
usage: binlog2sql.py [-h HOST] [-u USER] [-p [PASSWORD [PASSWORD ...]]]
[-P PORT] [--start-file START_FILE]
[--start-position START_POS] [--stop-file END_FILE]
[--stop-position END_POS] [--start-datetime START_TIME]
[--stop-datetime STOP_TIME] [--stop-never] [--help]
[-d [DATABASES [DATABASES ...]]]
[-t [TABLES [TABLES ...]]] [--only-dml]
[--sql-type [SQL_TYPE [SQL_TYPE ...]]] [-K] [-B]
[--back-interval BACK_INTERVAL]
Parse MySQL binlog to SQL you want
......
3.3 Mysql数据库前期操作
1) Mysql 必备参数
根据binlog2sql的README提示，确保Mysql的参数齐全无误。
1[root@PYMY-DB02 ~]# cat /etc/my.cnf |grep -Ei "server|log_bin|max_binlog_size|binlog_format|binlog_row_image"
2server-id = 117140
3character_set_server = utf8mb4
4log_bin = /mysql/log/binlog/mysql-bin.log
5max_binlog_size = 1G
6binlog_format = row
7binlog_row_image = full
2) 启动Mysql数据库
1[root@PYMY-DB02 ~]# service mysqld start
2Starting MySQL..... SUCCESS!
3) 创建数据库
创建一个flashbase的数据库，用于演示。
1(root@localhost) [(none)]>create database flashbase;
2Query OK, 1 row affected (0.00 sec)
4) 创建用户
创建admin用户专门用于快速恢复测试。
### 创建用户
(root@localhost) [(none)]>create user admin@'%' identified by "Mys_1234";
Query OK, 0 rows affected (0.00 sec)
### 给用户授权
(root@localhost) [(none)]>grant SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* to'admin'@'%';
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [(none)]>grant all privileges on flashbase.* to admin@'%';
Query OK, 0 rows affected (0.00 sec)
### 查看权限
(root@localhost) [(none)]>show grants for admin@'%';
+---------------------------------------------------------------------------+
| Grants for admin@% |
+---------------------------------------------------------------------------+
| GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'admin'@'%' |
| GRANT ALL PRIVILEGES ON `flashbase`.* TO 'admin'@'%' |
+---------------------------------------------------------------------------+
5) 使用flashbase数据库
### 使用flashbase数据库
(root@localhost) [(none)]> use flashbase;
Database changed
6) 插入第一波数据(代码从左往右滑动)
### id 1-5 为第一波数据，即正常数据。
create table glory_user (id int,name varchar(30),create_time datetime);
insert into glory_user values (1,'老亚瑟',now()-100);
insert into glory_user values (2,'项羽',now()-1100);
insert into glory_user values (3,'东方曜','2020-11-07 15:31:55');
insert into glory_user values (4,'李白','2019-01-03 01:09:21');
insert into glory_user values (5,'李信','2010-08-13 19:01:06');
commit;
### 查询数据
(root@localhost) [flashbase]>select * from glory_user;
+------+-----------+---------------------+
| id | name | create_time |
+------+-----------+---------------------+
| 1 | 老亚瑟 | 2020-11-08 13:13:51 |
| 2 | 项羽 | 2020-11-08 13:03:51 |
| 3 | 东方曜 | 2020-11-07 15:31:55 |
| 4 | 李白 | 2019-01-03 01:09:21 |
| 5 | 李信 | 2010-08-13 19:01:06 |
+------+-----------+---------------------+
5 rows in set (0.00 sec)查看当前时间，方便在事务里面查找记录
(root@localhost) [flashbase]>select now();
+---------------------+
| now() |
+---------------------+
| 2020-11-08 13:15:24 |
+---------------------+7) 过一段时间，开始模拟误操作
insert into glory_user values (6,'风暴龙王','1999-09-09 09:09:09');
insert into glory_user values (7,'鲁班七号','2000-01-01 01:01:01');
insert into glory_user values (8,'赔钱虎','2020-02-03 06:06:06');
delete from glory_user where id = 1;
commit;
查看误操作的时间
(root@localhost) [flashbase]>select now();
+---------------------+
| now() |
+---------------------+
| 2020-11-08 14:41:29 |
+---------------------+查看此时的binlog序号和position
(root@localhost) [flashbase]>show master status\G
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 9964
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: e2a5b55e-0e23-11eb-836d-0050563dd878:1-398) 业务期间的正常操作
### 更新数据，模拟DML频繁。update glory_user set name='曜' where id = 3;
commit;
### 查看表数据
(root@localhost) [flashbase]>select * from glory_user;
+------+--------------+---------------------+
| id | name | create_time |
+------+--------------+---------------------+
| 2 | 项羽 | 2020-11-08 13:03:51 |
| 3 | 曜 | 2020-11-07 15:31:55 |
| 4 | 李白 | 2019-01-03 01:09:21 |
| 5 | 李信 | 2010-08-13 19:01:06 |
| 6 | 风暴龙王 | 1999-09-09 09:09:09 |
| 7 | 鲁班七号 | 2000-01-01 01:01:01 |
| 8 | 赔钱虎 | 2020-02-03 06:06:06 |
+------+--------------+---------------------+
注意，此操作的时间并非误操作。
(root@localhost) [flashbase]>select now();
+---------------------+
| now() |
+---------------------+
| 2020-11-08 14:43:14 |
+---------------------+3.4 查找误操作的事务
1) 通过mysqlbinlog查找误操作的事务
误操作的时间为：2020-11-08 14:40:00 至 2020-11-08 14:44:00
[mysql@PYMY-DB02 ~]$ mysqlbinlog --start-datetime='2020-11-8 14:40:00' --stop-datetime='2020-11-8 14:44:00' -dflashbase -v /mysql/log/binlog/mysql-bin.000004 |grep "###"
### INSERT INTO `flashbase`.`glory_user`
### SET
### @1=6
### @2='风暴龙王'
### @3='1999-09-09 09:09:09'
### INSERT INTO `flashbase`.`glory_user`
### SET
### @1=7
### @2='鲁班七号'
### @3='2000-01-01 01:01:01'
### INSERT INTO `flashbase`.`glory_user`
### SET
### @1=8
### @2='赔钱虎'
### @3='2020-02-03 06:06:06'
### DELETE FROM `flashbase`.`glory_user`
### WHERE
### @1=1
### @2='老亚瑟'
### @3='2020-11-08 13:13:51'
### UPDATE `flashbase`.`glory_user`
### WHERE
### @1=3
### @2='东方曜'
### @3='2020-11-07 15:31:55'
### SET
### @1=3
### @2='曜'
### @3='2020-11-07 15:31:55'
2) 了解binlog2sql的参数选项
了解以下参数，对使用binlog2sql工具非常有帮助。
3) binlog2sql找到对应的SQL语句(代码从左往右滑动)
[root@PYMY-DB02 binlog2sql]# python3 binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'Mys_1234' -dflashbase -tglory_user --start-file='mysql-bin.000004' --start-datetime='2020-11-8 14:40:00' --stop-datetime='2020-11-8 14:44:00'
INSERT INTO `flashbase`.`glory_user`(`id`, `name`, `create_time`) VALUES (6, '风暴龙王', '1999-09-09 09:09:09'); #start 8859 end 9057 time 2020-11-08 14:40:43
INSERT INTO `flashbase`.`glory_user`(`id`, `name`, `create_time`) VALUES (7, '鲁班七号', '2000-01-01 01:01:01'); #start 9153 end 9351 time 2020-11-08 14:40:43
INSERT INTO `flashbase`.`glory_user`(`id`, `name`, `create_time`) VALUES (8, '赔钱虎', '2020-02-03 06:06:06'); #start 9447 end 9642 time 2020-11-08 14:40:43
DELETE FROM `flashbase`.`glory_user` WHERE `id`=1 AND `name`='老亚瑟' AND `create_time`='2020-11-08 13:13:51' LIMIT 1; #start 9738 end 9933 time 2020-11-08 14:40:44
UPDATE `flashbase`.`glory_user` SET `id`=3, `name`='曜', `create_time`='2020-11-07 15:31:55' WHERE `id`=3 AND `name`='东方曜' AND `create_time`='2020-11-07 15:31:55' LIMIT 1; #start 10029 end 10239 time 2020-11-08 14:43:01
### 加选项 "-B",生成回滚语句
[root@PYMY-DB02 binlog2sql]# python3 binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'Mys_1234' -dflashbase -tglory_user --start-file='mysql-bin.000004' --start-datetime='2020-11-8 14:40:00' --stop-datetime='2020-11-8 14:44:00' -B > 201108_1440_1444_rollback.sql
### 回滚语句
[root@PYMY-DB02 binlog2sql]# cat 201108_1440_1444_rollback.sql
UPDATE `flashbase`.`glory_user` SET `id`=3, `name`='东方曜', `create_time`='2020-11-07 15:31:55' WHERE `id`=3 AND `name`='曜' AND `create_time`='2020-11-07 15:31:55' LIMIT 1; #start 10029 end 10239 time 2020-11-08 14:43:01
INSERT INTO `flashbase`.`glory_user`(`id`, `name`, `create_time`) VALUES (1, '老亚瑟', '2020-11-08 13:13:51'); #start 9738 end 9933 time 2020-11-08 14:40:44
DELETE FROM `flashbase`.`glory_user` WHERE `id`=8 AND `name`='赔钱虎' AND `create_time`='2020-02-03 06:06:06' LIMIT 1; #start 9447 end 9642 time 2020-11-08 14:40:43
DELETE FROM `flashbase`.`glory_user` WHERE `id`=7 AND `name`='鲁班七号' AND `create_time`='2000-01-01 01:01:01' LIMIT 1; #start 9153 end 9351 time 2020-11-08 14:40:43
DELETE FROM `flashbase`.`glory_user` WHERE `id`=6 AND `name`='风暴龙王' AND `create_time`='1999-09-09 09:09:09' LIMIT 1; #start 8859 end 9057 time 2020-11-08 14:40:43
### 回退操作
(root@localhost) [flashbase]>INSERT INTO `flashbase`.`glory_user`(`id`, `name`, `create_time`) VALUES (1, '老亚瑟', '2020-11-08 13:13:51');
Query OK, 1 row affected (0.00 sec)
(root@localhost) [flashbase]>DELETE FROM `flashbase`.`glory_user` WHERE `id`=8 AND `name`='赔钱虎' AND `create_time`='2020-02-03 06:06:06' LIMIT 1;
Query OK, 1 row affected (0.01 sec)
(root@localhost) [flashbase]>DELETE FROM `flashbase`.`glory_user` WHERE `id`=7 AND `name`='鲁班七号' AND `create_time`='2000-01-01 01:01:01' LIMIT 1;
Query OK, 1 row affected (0.01 sec)
(root@localhost) [flashbase]>DELETE FROM `flashbase`.`glory_user` WHERE `id`=6 AND `name`='风暴龙王' AND `create_time`='1999-09-09 09:09:09' LIMIT 1;
Query OK, 1 row affected (0.01 sec)
### 查询表数据
(root@localhost) [flashbase]>select * from glory_user;
+------+-----------+---------------------+
| id | name | create_time |
+------+-----------+---------------------+
| 2 | 项羽 | 2020-11-08 13:03:51 |
| 3 | 曜 | 2020-11-07 15:31:55 |
| 4 | 李白 | 2019-01-03 01:09:21 |
| 5 | 李信 | 2010-08-13 19:01:06 |
| 1 | 老亚瑟 | 2020-11-08 13:13:51 |
+------+-----------+---------------------+
第四节 一些疑问
为什么binlog_format一定要设置成row模式呢？而不是MIXED呢？
为什么闪回只有DML操作，却没有DDL操作呢？
mysql-replication这个pip包究竟有什么用呢？
为什么binlog2sql好像跟mysqlbinlog高度兼容，不需要太多的代码就可以直接调用呢？
待小安Sir亲自体验一把，下次再分享下，敬请期待。
对了，故事的结尾就是小明对DBA微微一笑。
最后，为了方便大家能够直接下载这个binlog2sql的工具，有需要的亲们请在后台回复：binlog2sql，就可以获取下载地址。
binlog2sql作者：danfengcao (美团DBA团队)
下载链接 ：https://github.com/danfengcao/binlog2sql
往期回顾 ·
自动化运维实战五 | 【演示】3分钟一键部署Mysql数据库
自动化运维实战四 | 【实现】Mysql一键部署的前后端交互
自动化运维实战三 | 【规划】Mysql一键部署的数据模型
自动化运维实战二 | 【构思】Mysql一键部署的界面
自动化运维实战一 | 【模板】 套用精美模板演示登陆界面
喜欢就点个 在看 呀！